﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
namespace DataAccess.Service
{
    public class ChartService : IChartService
    {
        public DataSet getAcademyStatisticInfo()
        {
            String commandText = "select acaName as '学院名称',SUM(unPaidNum) as '未缴人数',SUM(paidNum) as '已缴人数' " +
                                "from dbo.Statistic_Grade_Tuition " +
                                //"where paymentYear = '2010' " +
                                "group by acaName";
            return SQLHelper.ExecuteDataSet(commandText);
        }


        public DataSet getAcademyStatisticInfo(String acaId)
        {
            String commandText = " select majName as '专业名称',SUM(unPaidNum) as '专业总未缴人数',SUM(paidNum) as '专业总已缴人数' " +
                                 "from Statistic_Grade_Tuition " +
                                 "where acaId = @acaId " +
                                 "group by majName";
            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("@acaId", acaId),
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }

        public DataSet getMajorStatisticInfo(String acaId, String majId)
        {
            String commandText = "select grade as '年级',SUM(unPaidNum) as '年级未缴总人数',SUM(paidNum) as '年级已缴总人数' " +
                                "from Statistic_Grade_Tuition " +
                                "where acaId = @acaId " +
                                 "and majId = @majId " +
                                 "group by majName,grade";
            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("@acaId", acaId),
                 new SqlParameter("@majId", majId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }


        public DataSet getGradeStatisticInfo(String para)
        {
            String commandText = "select className as '班级名称',SUM(unPaidNum) as '班级未缴总人数',SUM(paidNum) as '班级已缴缴总人数' " + 
                    "from Statistic_Class_Tuition " +
                  "  where classId like @para " + 
                   " group by className";
            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("@para", para + "%")
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }


        public DataSet getClassStatisticInfo(String para)
        {

            String commandText = "select paymentYear as '缴费年份',SUM(unPaidNum) as '该年未缴总人数',SUM(paidNum) as '该年已缴总人数' " +
                                 "from Statistic_Class_Tuition " +
                                 "where classId = @para " +
                                 "group by paymentYear";
            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("@para", para)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }
    }
}
